﻿SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
GO
ALTER PROCEDURE [dbo].[bhp_NemoBlogPosts_GetSimilars](
	@PostId				INT,
	@Count				INT,
	@PastDays			INT,
	@ForwardDays	INT,
	@Language			NCHAR(5) = 'pl-PL'
)
AS
BEGIN

	DECLARE
		@Sim	TABLE (
			PostId			INT,
			Value			INT,
			PublicDate	INT
		)

	DECLARE
		@PostPastDaysU		INT,
		@PostForwardDaysU	INT

	SELECT
		@PostPastDaysU = dbo.bhf_ConvertDateTimeTo(DATEADD(DAY, @PastDays * (-1), dbo.bhf_ConvertDateTimeFrom(publishDate))),
		@PostForwardDaysU = dbo.bhf_ConvertDateTimeTo(DATEADD(DAY, @ForwardDays, dbo.bhf_ConvertDateTimeFrom(publishDate)))
	FROM
		BLOG_POSTS
	WHERE
		idx = @PostId

	INSERT INTO @Sim
		SELECT idx, 0, publishDate FROM BLOG_POSTS WHERE del = 0 AND published = 1 AND publishDate IS NOT NULL AND idx <> @PostId AND publishDate BETWEEN @PostPastDaysU AND @PostForwardDaysU

	UPDATE
		@Sim 
	SET
		Value = x.TagCount
	FROM
		@Sim s
		INNER JOIN
			(SELECT
				p.PostId PostId, COUNT(rt2.rel) TagCount
			FROM
				BLOG_POSTS_tags rt
				INNER JOIN BLOG_TAGS t ON rt.rel = t.idx
				INNER JOIN BLOG_POSTS_tags rt2 ON rt.rel = rt2.rel
				INNER JOIN @Sim p ON rt2.idx = p.PostId
			WHERE
				rt.idx = @PostId
				AND rt.del = 0
				AND t.del = 0
			GROUP BY p.PostId) x ON s.PostId = x.PostId

	SELECT TOP(@Count) 
		p.*
	FROM 
		@Sim s
		INNER JOIN BLOG_POSTS p ON s.PostId = p.idx
	ORDER BY 
		s.Value DESC, 
		p.publishDate DESC
		
END
